﻿Imports System.Data.SqlClient
Imports SchoolClick.Database.UDT

Namespace SchoolClick.Database.DAL

    Public Interface IMatricula
        ReadOnly Property IMatricula As IMatricula

        Function IsPeriodoMatricula() As Boolean
        Function GetInfoPeriodoMatricula(year As Integer) As PERIODOS_MATRICULA
        Function GetInfoFechasMatricula() As PERIODOS_MATRICULA
        Function GetGradosDisponibles() As IEnumerable(Of V_MAT_GRADOS_DISPONIBLES)
        Function GetGradosDisponibles(year As Integer) As IEnumerable(Of V_MAT_GRADOS_DISPONIBLES)
        Function GetPaises() As IEnumerable(Of PAIS)
        Function GetCentrosEscolares() As IEnumerable(Of CENTROS_EDUCATIVOS)
        Function GetMunicipios() As IEnumerable(Of MUNICIPIO)
        Function IsExistNIE(NIE As String) As Boolean
        Function InsertNewSolicitud(solicitud As MATRICULA_SOLICITUDES, responsables As UDT_MATRICULA_RESPONSABLES) As Integer
        Function CheckNIEandSolicitud(solicitud As Integer, nie As Integer) As Boolean
        Function GetPreseleccion(Grado As Integer) As IEnumerable(Of V_MAT_ALUMNOS_PRESELECCIONADOS)
        Function ShowPreseleccion() As Boolean
        Function ShowAlumnosAdmitidos(year As Integer) As Boolean

        'FUNCIONES DE REGISTROS ACADEMICO
        Function IsExistYearMatricula(year As String) As Boolean
        Function GetResumenSolicitudesRecibidas(año As String) As IEnumerable(Of RESUMEN_SOLICITUDES_NUEVO_INGRESO)
        Sub UpdateInfoPeriodoNuevoIngreso(year As String, periodo As InfoPeriodoNuevoIngreso)
        Sub InsertInfoPeriodoNuevoIngreso(year As Integer, periodo As InfoPeriodoNuevoIngreso)
        Function GetSolicitudNuevoIngreso(NIE As String, year As String) As REP_SOLICITUD_NUEVO_INGRESO
        Function GetGradoAplica(NIE As String, year As String) As String
        Sub UpdateSolicitudPreselect(year As Integer, NIE As String, activar As Boolean)
        Sub DeleteSolicitud(year As Integer, NIE As String)
        Sub InsertNotaExamen(Solicitud As Integer, Nota As Decimal)
        Function GetNotasPreseleccion(Grado As Integer) As IEnumerable(Of V_MAT_NOTAS_ALUMNOS_PRESELECCIONADOS)
        Function GetResumenGralNuevoIngreso(year As Integer) As RESUMEN_PNUEVO_INGRESO
    End Interface

    Partial Public Class SchoolClickDAL
        Implements IMatricula

        Public ReadOnly Property IMatricula As IMatricula Implements IMatricula.IMatricula
            Get
                Return DirectCast(Me, IMatricula)
            End Get
        End Property

        Private Function IsPeriodoMatricula() As Boolean Implements IMatricula.IsPeriodoMatricula
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.MATRI_ISPERIODOACTIVO.Single
                Catch ex As Exception
                    ex.Data.Clear()
                    Return False
                End Try
            End Using
        End Function

        Private Function GetInfoPeriodoMatricula(year As Integer) As PERIODOS_MATRICULA Implements IMatricula.GetInfoPeriodoMatricula
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.PERIODOS_MATRICULA.Where(Function(m) m.ANIO_MATRICULA = year).SingleOrDefault
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Function GetInfoFechasMatricula() As PERIODOS_MATRICULA Implements IMatricula.GetInfoFechasMatricula
            Dim yearM = Date.Now.AddYears(1).Year
            Return Me.GetInfoPeriodoMatricula(yearM)
        End Function

        Private Function GetGradosDisponibles() As IEnumerable(Of V_MAT_GRADOS_DISPONIBLES) Implements IMatricula.GetGradosDisponibles
            Dim yearM = Date.Now.AddYears(1).Year
            Return GetGradosDisponibles(yearM)
        End Function

        Private Function GetGradosDisponibles(year As Integer) As IEnumerable(Of V_MAT_GRADOS_DISPONIBLES) Implements IMatricula.GetGradosDisponibles
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.V_MAT_GRADOS_DISPONIBLES.Where(Function(gd) gd.ANIO_MATRICULA = year) _
                                                       .OrderByDescending(Function(gd) gd.ESPECIALIDAD) _
                                                       .ThenBy(Function(gd) gd.GRADO) _
                                                       .ToArray()
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Function GetPaises() As IEnumerable(Of PAIS) Implements IMatricula.GetPaises
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.PAISES.OrderBy(Function(p) p.PAI_NOMBRE) _
                                     .ToArray()
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Function GetCentrosEscolares() As IEnumerable(Of CENTROS_EDUCATIVOS) Implements IMatricula.GetCentrosEscolares
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.CENTROS_EDUCATIVOS.OrderBy(Function(ce) ce.NOMBRE_CEDUCATIVO) _
                                     .ToArray()
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Function GetMunicipios() As IEnumerable(Of MUNICIPIO) Implements IMatricula.GetMunicipios
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.MUNICIPIOS.OrderBy(Function(m) m.ID_DEPARTAMENTO) _
                                         .ThenBy(Function(m) m.NOMBRE_MUNICIPIO) _
                                         .ToArray()
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Function IsExistNIE(NIE As String) As Boolean Implements IMatricula.IsExistNIE
            Using cnn As New SchoolClickEntities
                Try
                    Dim year = Date.Now.AddYears(1).Year
                    Dim count = cnn.MATRICULA_SOLICITUDES.Where(Function(ms) ms.NIE = NIE And ms.PMATRICULA_SOLICITUD = year).Count
                    Return If(count.Equals(0), False, True)
                Catch ex As Exception
                    Return True
                End Try
            End Using
        End Function

        Private Function InsertNewSolicitud(solicitud As MATRICULA_SOLICITUDES, responsables As UDT_MATRICULA_RESPONSABLES) As Integer Implements IMatricula.InsertNewSolicitud
            'Preparamos la solicitud para ser enviada al servidor con cada uno de los parametros
            Dim IDGrado As New SqlParameter("IDGRADO", SqlDbType.Int) : IDGrado.Value = solicitud.ID_GRADO_APLICA
            Dim Nombres As New SqlParameter("NOMBRES", SqlDbType.VarChar, 50) : Nombres.Value = solicitud.NOMBRES.ToInitCap
            Dim Apellidos As New SqlParameter("APELLIDOS", SqlDbType.VarChar, 50) : Apellidos.Value = solicitud.APELLIDOS.ToInitCap
            Dim Genero As New SqlParameter("GENERO", SqlDbType.Char, 1) : Genero.Value = solicitud.GENERO
            Dim NIE As New SqlParameter("NIE", SqlDbType.VarChar, 16) : NIE.Value = solicitud.NIE
            Dim DUI As New SqlParameter("DUI", SqlDbType.VarChar, 10) : DUI.Value = If(String.IsNullOrWhiteSpace(solicitud.DUI), DBNull.Value, solicitud.DUI)
            Dim IDPais As New SqlParameter("IDPAIS", SqlDbType.Int) : IDPais.Value = solicitud.ID_PAIS
            Dim Fecha_Nac As New SqlParameter("FECHA_NAC", SqlDbType.Date) : Fecha_Nac.Value = solicitud.FECHA_NACIMIENTO
            Dim IsParvularia As New SqlParameter("ISPARVULARIA", SqlDbType.Bit) : IsParvularia.Value = solicitud.ESTUDIO_PARVULARIA
            Dim IDInstituto As New SqlParameter("IDINSTITUTO", SqlDbType.Int) : IDInstituto.Value = solicitud.ID_CEDUCATIVO_PROCEDENCIA
            Dim Direccion As New SqlParameter("DIRECCION", SqlDbType.VarChar, 150) : Direccion.Value = solicitud.DIRECCION_DE_CASA.ToInitCap
            Dim IDDepartamento As New SqlParameter("IDDEPARTAMENTO", SqlDbType.Int) : IDDepartamento.Value = solicitud.DEPARTAMENTO
            Dim IDMunicipio As New SqlParameter("IDMUNICIPIO", SqlDbType.Int) : IDMunicipio.Value = solicitud.MUNICIPIO
            Dim Telefono As New SqlParameter("TELEFONO", SqlDbType.VarChar, 9) : Telefono.Value = solicitud.TELEFONO
            Dim Celular As New SqlParameter("CELULAR", SqlDbType.VarChar, 9) : Celular.Value = If(String.IsNullOrWhiteSpace(solicitud.CELULAR), DBNull.Value, solicitud.CELULAR)
            Dim EMail As New SqlParameter("EMAIL", SqlDbType.VarChar, 100) : EMail.Value = solicitud.CORREO_ELECTRONICO.ToLower
            Dim NoMiembros As New SqlParameter("NOMIEMBROS", SqlDbType.Int) : NoMiembros.Value = solicitud.MIEMBROS_FAMILIA
            Dim IsTrabaja As New SqlParameter("ISTRABAJA", SqlDbType.Bit) : IsTrabaja.Value = solicitud.TRABAJA
            Dim IsHijos As New SqlParameter("ISHIJOS", SqlDbType.Bit) : IsHijos.Value = solicitud.TIENE_HIJOS
            Dim Enfermedades As New SqlParameter("ENFERMEDADES", SqlDbType.VarChar, 250) : Enfermedades.Value = If(String.IsNullOrWhiteSpace(solicitud.ENFERM_PADECE), DBNull.Value, solicitud.ENFERM_PADECE.ToInitCap)
            Dim Medicamento As New SqlParameter("MEDICAMENTO", SqlDbType.VarChar, 250) : Medicamento.Value = If(String.IsNullOrWhiteSpace(solicitud.MEDICAMENTO_PERMANENTE), DBNull.Value, solicitud.MEDICAMENTO_PERMANENTE.ToInitCap)
            Dim AnioAnterior As New SqlParameter("ANIOANTERIOR", SqlDbType.Char, 1) : AnioAnterior.Value = solicitud.ANIO_ANTERIOR
            Dim UbicacionGeografica As New SqlParameter("UBICACIONGEOGRAFICA", SqlDbType.Char, 1) : UbicacionGeografica.Value = solicitud.UBICACION_GEOGRAFICA
            Dim ViveCon As New SqlParameter("VIVECON", SqlDbType.Char, 2) : ViveCon.Value = solicitud.VIVE_CON
            Dim AnioCursoUG As New SqlParameter("ANIOCURSOUG", SqlDbType.Char, 4) : AnioCursoUG.Value = solicitud.ANIO_CURSO_UG

            'Creamos el parametro que nos va a regresar el No. de Solicitud
            Dim NoSolicitud As New SqlParameter("NOSOLICITUD", SqlDbType.Int)
            NoSolicitud.Direction = ParameterDirection.Output

            'Ahora creamos una nueva instancia de nuestro EFEntities para poder enviar los datos al servidor
            Dim DAL As New SchoolClickEntities
            Try
                DAL.ExecuteStoreProcedure(responsables.ToString, IDGrado, Nombres, Apellidos, Genero, NIE, DUI, IDPais, Fecha_Nac,
                                          IsParvularia, IDInstituto, Direccion, IDDepartamento, IDMunicipio, Telefono, Celular, EMail,
                                          NoMiembros, IsTrabaja, IsHijos, Enfermedades, Medicamento, AnioAnterior, UbicacionGeografica,
                                          ViveCon, AnioCursoUG, responsables.GetSQLParameter, NoSolicitud)

                'Recuperamos el valor devuelto por el sistema
                If Not IsDBNull(NoSolicitud.Value) Then
                    Return NoSolicitud.Value
                Else
                    Throw New NullReferenceException("No se pudo recuperar del sistema el No. de solicitud que le fue asignado, pero los datos fueron guardados en nuestro servidor.")
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Private Function CheckNIEandSolicitud(solicitud As Integer, nie As Integer) As Boolean Implements IMatricula.CheckNIEandSolicitud
            Using cnn As New SchoolClickEntities
                Try
                    Dim year = Date.Now.AddYears(1).Year
                    Dim count = cnn.MATRICULA_SOLICITUDES.Where(Function(ms) ms.NIE = nie And ms.PMATRICULA_SOLICITUD = year And ms.ID_SOLICITUD = solicitud).Count
                    Return If(count.Equals(0), False, True)
                Catch ex As Exception
                    Return False
                End Try
            End Using
        End Function

        Private Function GetPreseleccion(Grado As Integer) As IEnumerable(Of V_MAT_ALUMNOS_PRESELECCIONADOS) Implements IMatricula.GetPreseleccion
            Using cnn As New SchoolClickEntities
                Try
                    Dim year = Date.Now.AddYears(1).Year
                    Return cnn.V_MAT_ALUMNOS_PRESELECCIONADOS.Where(Function(p) p.ANIO = year And p.ID_GRADO = Grado).ToArray
                Catch ex As Exception
                    Return New V_MAT_ALUMNOS_PRESELECCIONADOS() {}
                End Try
            End Using
        End Function

        Private Function ShowPreseleccion() As Boolean Implements IMatricula.ShowPreseleccion
            Using cnn As New SchoolClickEntities
                Try
                    Dim year = Date.Now.AddYears(1).Year
                    Return cnn.PERIODOS_MATRICULA.Where(Function(p) p.ANIO_MATRICULA = year) _
                                                 .Select(Function(p) p.SHOW_PRESELECCION) _
                                                 .Single
                Catch ex As Exception
                    Return False
                End Try
            End Using
        End Function

        Private Function GetResumenSolicitudesRecibidas(año As String) As IEnumerable(Of RESUMEN_SOLICITUDES_NUEVO_INGRESO) Implements IMatricula.GetResumenSolicitudesRecibidas
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.GET_RESUMEN_SOLICITUDES_NUEVO_INGRESO(año) _
                                    .OrderByDescending(Function(gd) gd.ESPECIALIDAD) _
                                    .ThenBy(Function(gd) gd.GRADO) _
                                    .ToArray()
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Sub UpdateInfoPeriodoNuevoIngreso(year As String, periodo As InfoPeriodoNuevoIngreso) Implements IMatricula.UpdateInfoPeriodoNuevoIngreso
            'Preparamos las fecha para poder ser enviadas
            Dim FInicio As Date = New Date(periodo.FECHA_INICIO.Year, periodo.FECHA_INICIO.Month, periodo.FECHA_INICIO.Day,
                                           periodo.HORA_INICIO.Hour, periodo.HORA_INICIO.Minute, 0),
                FFinal As Date = New Date(periodo.FECHA_FINALIZACION.Year, periodo.FECHA_FINALIZACION.Month, periodo.FECHA_FINALIZACION.Day,
                                           periodo.HORA_FINALIZACION.Hour, periodo.HORA_FINALIZACION.Minute, 0)

            Using cnn As New SchoolClickEntities
                Try
                    cnn.MATRICULA_UPT_INFO_NUEVOINGRESO(year, FInicio, FFinal, periodo.SHOW_LIST_PRESELECCION, periodo.SHOW_LIST_SELECCIONADOS,
                                                        ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
                Catch ex As EntityCommandExecutionException
                    Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Sub

        Private Function IsExistYearMatricula(year As String) As Boolean Implements IMatricula.IsExistYearMatricula
            Using cnn As New SchoolClickEntities
                Try
                    Return If(cnn.PERIODOS_MATRICULA.Where(Function(pm) pm.ANIO_MATRICULA = year).Count.Equals(0), False, True)
                Catch ex As Exception
                    ex.Data.Clear()
                    Return False
                End Try
            End Using
        End Function

        Private Sub InsertInfoPeriodoNuevoIngreso(year As Integer, periodo As InfoPeriodoNuevoIngreso) Implements IMatricula.InsertInfoPeriodoNuevoIngreso
            'Preparamos las fecha para poder ser enviadas
            Dim FInicio As Date = New Date(periodo.FECHA_INICIO.Year, periodo.FECHA_INICIO.Month, periodo.FECHA_INICIO.Day,
                                           periodo.HORA_INICIO.Hour, periodo.HORA_INICIO.Minute, 0),
                FFinal As Date = New Date(periodo.FECHA_FINALIZACION.Year, periodo.FECHA_FINALIZACION.Month, periodo.FECHA_FINALIZACION.Day,
                                           periodo.HORA_FINALIZACION.Hour, periodo.HORA_FINALIZACION.Minute, 0)

            Using cnn As New SchoolClickEntities
                Try
                    cnn.MATRICULA_INS_INFO_NUEVOINGRESO(year, FInicio, FFinal, periodo.SHOW_LIST_PRESELECCION, periodo.SHOW_LIST_SELECCIONADOS,
                                                        ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
                Catch ex As EntityCommandExecutionException
                    Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Sub

        Private Function GetSolicitudNuevoIngreso(NIE As String, year As String) As REP_SOLICITUD_NUEVO_INGRESO Implements IMatricula.GetSolicitudNuevoIngreso
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.REP_SOLICITUD_NUEVO_INGRESO.Where(Function(ms) ms.PMATRICULA_SOLICITUD = year And ms.NIE = NIE).Single
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Function GetGradoAplica(NIE As String, year As String) As String Implements IMatricula.GetGradoAplica
            Using cnn As New SchoolClickEntities
                Try
                    Dim data = cnn.MATRICULA_SOLICITUDES.Where(Function(ms) ms.PMATRICULA_SOLICITUD = year And ms.NIE = NIE) _
                                                        .Join(cnn.MATRICULA_GRADOS,
                                                              Function(ms) ms.ID_GRADO_APLICA,
                                                              Function(mg) mg.ID_GRADOS_DISPONIBLES,
                                                              Function(ms, mg) New With {.Titulo = mg.TITULO}).SingleOrDefault
                    Return data.Titulo
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function

        Private Sub UpdateSolicitudPreselect(year As Integer, NIE As String, activar As Boolean) Implements IMatricula.UpdateSolicitudPreselect
            Using cnn As New SchoolClickEntities
                Try
                    cnn.MATRICULA_ON_OR_OFF_SOLICITUD_NUEVO_INGRESO(year, NIE, activar,
                                                        ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
                Catch ex As EntityCommandExecutionException
                    Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Sub

        Private Sub DeleteSolicitud(year As Integer, NIE As String) Implements IMatricula.DeleteSolicitud
            Using cnn As New SchoolClickEntities
                Try
                    cnn.MATRICULA_DEL_SOLICITUD_NUEVOINGRESO(year, NIE, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
                Catch ex As EntityCommandExecutionException
                    Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Sub

        Private Sub InsertNotaExamen(Solicitud As Integer, Nota As Decimal) Implements IMatricula.InsertNotaExamen
            Using cnn As New SchoolClickEntities
                Try
                    cnn.MATRICULA_INS_NOTA_EXA(Solicitud, Nota, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
                Catch ex As EntityCommandExecutionException
                    Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Sub

        Private Function GetNotasPreseleccion(Grado As Integer) As IEnumerable(Of V_MAT_NOTAS_ALUMNOS_PRESELECCIONADOS) Implements IMatricula.GetNotasPreseleccion
            Using cnn As New SchoolClickEntities
                Try
                    Dim year = Date.Now.AddYears(1).Year
                    Return cnn.V_MAT_NOTAS_ALUMNOS_PRESELECCIONADOS.Where(Function(p) p.ANIO = year And p.ID_GRADO = Grado).ToArray
                Catch ex As Exception
                    Return New V_MAT_NOTAS_ALUMNOS_PRESELECCIONADOS() {}
                End Try
            End Using
        End Function

        Private Function ShowAlumnosAdmitidos(year As Integer) As Boolean Implements IMatricula.ShowAlumnosAdmitidos
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.PERIODOS_MATRICULA.Where(Function(p) p.ANIO_MATRICULA = year) _
                                                 .Select(Function(p) p.SHOW_QUEDARON) _
                                                 .Single
                Catch ex As Exception
                    Return False
                End Try
            End Using
        End Function

        Private Function GetResumenGralNuevoIngreso(year As Integer) As RESUMEN_PNUEVO_INGRESO Implements IMatricula.GetResumenGralNuevoIngreso
            Using cnn As New SchoolClickEntities
                Try
                    Return cnn.GET_RESUMEN_PNUEVO_INGRESO(year).SingleOrDefault
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Function
    End Class
End Namespace
